<?php

class cronActions extends sfActions {

    public function executeIndex(sfWebRequest $request){
        $this->updateAccounts();
        $this->updateArticles();
        return sfView::NONE;
    }

    private function getHoursDiff($date1, $date2){
        $diff = abs(strtotime($date1) - strtotime($date2));

        $years = floor($diff / (365*60*60*24));
        $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
        $days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
        $hours = $days*24;
        //printf("%d years, %d months, %d days, %d hours\n", $years, $months, $days, $hours);
        return $hours;
    }

    private function updateArticles(){
        $c = new Criteria();
        $c->addAscendingOrderByColumn(ArticlePeer::CREATED_AT);
        $c->setLimit(1);
        $primer_articulo = ArticlePeer::doSelectOne($c);

        $today = date("Y-m-d H:i:s");
        $first_date = $primer_articulo->getCreatedAt();

        $max_antiguedad_horas = $this->getHoursDiff($today, $first_date);//100%
        $max_score = 5;//100%
        $max_lecturas = $this->getArticuloMasLeido();

        $articulosLecturas = $this->getArticulosLecturasCount();

        $c->clear();
        $articulos = ArticlePeer::doSelect($c);
        foreach($articulos as $articulo){
            /* TIEMPO */
            $diferencia_en_horas = $this->getHoursDiff($today, $articulo->getCreatedAt());
            $tiempo = ($diferencia_en_horas*100)/$max_antiguedad_horas;
            $rank1 = abs(100-$tiempo);
            /* END TIEMPO */

            /* ESTRELLAS */
            $score_actual = $articulo->getScore();
            $rank2 = ($score_actual*100)/$max_score;
            /* END ESTRELLAS */

            /* AUTOR */
            $rank3 = $articulo->getAccount()->getRank();
            /* END AUTOR */

            /* LEIDO PAGADO */
            $lecturas_count = 0;
            foreach($articulosLecturas as $al){
                if($al['id']==$articulo->getId()){
                    $lecturas_count = $al['lecturas'];
                }
            }
            $rank4 = ($lecturas_count*100)/$max_lecturas;
            /* END LEIDO PAGADO */

            /* RANK */
            $rank_calculado = $rank1*(44/100)+$rank2*(14/100)+$rank3*(28/100)+$rank4*(14/100);//%
            // se obtiene el 90% del resultado, ya que el ranking solo se puede poner entre 0 - 90
            $rank_recalculado = $rank_calculado*90/100;
            /* END RANK */

            $articulo->setRank($rank_recalculado);
            $articulo->save();
        }
    }

    private function updateAccounts(){
        $max_subscriptions = $this->getMaxSubscriptions();//100%
        $authorsSubscriptions = $this->getAuthorsSubscriptionsCount();

        $max_leido_pagado = $this->getMaxAutorMasLeido();
        $autoresLeidos = $this->getAutorMasLeido();

        $max_estrellas_x_autor = $this->getMaxEstrellasXAutor();
        $estrellasXAutor = $this->getEstrellasXAutor();
        
        $c = new Criteria();
        $authors = AccountPeer::doSelect($c);
        foreach($authors as $author){
            /* SUSCRIPCIONES */
            foreach($authorsSubscriptions as $as){
                if($as['id']==$author->getId()){
                    $subscriptions_count = $as['subscribers'];
                }
            }
            $rank1 = ($subscriptions_count*100)/$max_subscriptions;
            /* END SUSCRIPCIONES */
            
            /* LEIDO PAGADO */
            foreach($autoresLeidos as $al){
                if($al['owner_id']==$author->getId()){
                    $lecturas_count = $al['suma'];
                }
            }
            $rank2 = ($lecturas_count*100)/$max_leido_pagado;
            /* END LEIDO PAGADO */

            /* ESTRELLAS */
            foreach($estrellasXAutor as $exa){
                if($exa['owner_id']==$author->getId()){
                    $estrellas_result = $exa['result'];
                }
            }
            $rank3 = ($estrellas_result*100)/$max_estrellas_x_autor;
            /* END ESTRELLAS */

            /* RANK */
            $rank_calculado = $rank1*(14/100)+$rank2*(43/100)+$rank3*(43/100);//%
            // se obtiene el 90% del resultado, ya que el ranking solo se puede poner entre 0 - 90
            $rank_recalculado = $rank_calculado*90/100;
            /* END RANK */

            $author->setRank($rank_recalculado);
            $author->save();
        }
    }

    private function getMaxEstrellasXAutor(){
        $resp = null;
        $con = Propel::getConnection();
        $query = '
          SELECT MAX(tbl.result) AS max
          FROM (
            SELECT %s AS owner_id, CAST(SUM(%s)/COUNT(%s) AS DECIMAL(5,2)) AS result
            FROM %s
            GROUP BY owner_id
          ) AS tbl
        ';

        $query = sprintf($query,
            ArticlePeer::OWNER_ID,
            ArticlePeer::SCORE,
            ArticlePeer::SCORE,
            ArticlePeer::TABLE_NAME
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $resp = $rs['max'];
        }

        return $resp;
    }

    private function getEstrellasXAutor(){
        $authors = array();
        $con = Propel::getConnection();
        $query = '
            SELECT %s as owner_id, cast(sum(%s)/count(%s) AS DECIMAL(5,2)) as result
            FROM %s
            group by owner_id
        ';

        $query = sprintf($query,
            ArticlePeer::OWNER_ID,
            ArticlePeer::SCORE,
            ArticlePeer::SCORE,
            ArticlePeer::TABLE_NAME
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $authors[] = array(
                "owner_id" => $rs['owner_id'],
                "result" => $rs['result']
            );
        }

        return $authors;
    }

    private function getMaxAutorMasLeido(){
        $resp = null;
        $con = Propel::getConnection();
        $query = '
            SELECT MAX(tbl2.suma) as max
            FROM (
              SELECT tbl.article_id, tbl.owner_id, SUM(tbl.cantidad_lecturas) AS suma
              FROM (
                SELECT %s as article_id, %s as owner_id, COUNT(%s) AS cantidad_lecturas
                FROM %s
                LEFT JOIN %s
                ON %s = %s
                GROUP BY %s
                ORDER BY article_id ASC
              ) AS tbl
              GROUP BY tbl.owner_id
            ) AS tbl2
        ';

        $query = sprintf($query,
            ArticlePeer::ID,
            ArticlePeer::OWNER_ID,
            ArticlePaymentsPeer::ACCOUNT_ID,
            ArticlePeer::TABLE_NAME,
            ArticlePaymentsPeer::TABLE_NAME,
            ArticlePeer::ID,
            ArticlePaymentsPeer::ARTICLE_ID,
            ArticlePaymentsPeer::ARTICLE_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $resp = $rs['max'];
        }

        return $resp;
    }

    private function getAutorMasLeido(){
        $articles = array();
        $con = Propel::getConnection();
        $query = '
            SELECT tbl.article_id, tbl.owner_id, sum(tbl.cantidad_lecturas) AS suma
            FROM (
              SELECT %s AS article_id, %s AS owner_id, count(%s) AS cantidad_lecturas
              FROM %s
              LEFT JOIN %s
              ON %s = %s
              GROUP BY %s
              ORDER BY article_id asc) AS tbl
            GROUP BY tbl.owner_id
        ';

        $query = sprintf($query,
            ArticlePeer::ID,
            ArticlePeer::OWNER_ID,
            ArticlePaymentsPeer::ACCOUNT_ID,
            ArticlePeer::TABLE_NAME,
            ArticlePaymentsPeer::TABLE_NAME,
            ArticlePeer::ID,
            ArticlePaymentsPeer::ARTICLE_ID,
            ArticlePaymentsPeer::ARTICLE_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $articles[] = array(
                "article_id" => $rs['article_id'],
                "owner_id" => $rs['owner_id'],
                "suma" => $rs['suma']
            );
        }

        return $articles;
    }

    private function getMaxSubscriptions(){
        $resp = null;
        $con = Propel::getConnection();
        $query = '
          SELECT max(tbl.subscribers) as max
          FROM(
            SELECT count(%s) as subscribers
            FROM %s
            GROUP BY %s
            ORDER BY subscribers DESC
          ) as tbl
        ';

        $query = sprintf($query,
          AccountSubscriptionsPeer::SUBSCRIBER_ID,
          AccountSubscriptionsPeer::TABLE_NAME,
          AccountSubscriptionsPeer::USER_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $resp = $rs['max'];
        }

        return $resp;
    }

    private function getArticuloMasLeido(){
        $resp = null;
        $con = Propel::getConnection();
        $query = '
          SELECT max(tbl.lecturas) as max
          FROM(
            SELECT count(%s) as lecturas
            FROM %s
            GROUP BY %s
            ORDER BY lecturas DESC
          ) as tbl
        ';

        $query = sprintf($query,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::TABLE_NAME,
          ArticlePaymentsPeer::ARTICLE_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $resp = $rs['max'];
        }

        return $resp;
    }

    private function getAuthorsSubscriptionsCount(){
        $authors = array();
        $con = Propel::getConnection();
        $query = '
          SELECT %s as id, count(%s) as subscribers
          FROM %s
          GROUP BY %s
          ORDER BY subscribers DESC
        ';

        $query = sprintf($query,
          AccountSubscriptionsPeer::USER_ID,
          AccountSubscriptionsPeer::SUBSCRIBER_ID,
          AccountSubscriptionsPeer::TABLE_NAME,
          AccountSubscriptionsPeer::USER_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $authors[] = array(
                "id" => $rs['id'],
                "subscribers" => $rs['subscribers']
            );
        }

        return $authors;
    }

    private function getArticulosLecturasCount(){
        $articles = array();
        $con = Propel::getConnection();
        $query = '
          SELECT %s as id, count(%s) as lecturas
          FROM %s
          GROUP BY %s
          ORDER BY lecturas DESC
        ';

        $query = sprintf($query,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::TABLE_NAME,
          ArticlePaymentsPeer::ARTICLE_ID
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $articles[] = array(
                "id" => $rs['id'],
                "lecturas" => $rs['lecturas']
            );
        }

        return $articles;
    }

}